package com.quotemanager.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.nirvana.core.Validator;
import com.quotemanager.dataobject.ProductType;
import com.quotemanager.exception.DataAccessException;
import com.quotemanager.exception.EmptyParamException;
import com.quotemanager.util.ConnectionFactory;

public class ProductTypeDao {

	private static final String TABLE_NAME_PRODUCT_TYPE = "PRODUCT_TYPE";
	
	public int registerProductType(ProductType productType) throws DataAccessException {
		
		if(productType!=null && !Validator.isNullOrEmpty(productType.getProductTypeName())) {
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet result = null;
			try {
				String insertQuery = "INSERT INTO "+TABLE_NAME_PRODUCT_TYPE+"(PRODUCT_TYPE_NAME, PRODUCT_TYPE_DESCRIPTION) " +
						"VALUES(?,?)";
				connection = ConnectionFactory.getConnection();
				preparedStatement = connection.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
				preparedStatement.setString(1, productType.getProductTypeName());
				preparedStatement.setString(2, productType.getProductTypeDescription());
				int count = preparedStatement.executeUpdate();
				if(count>0) {
					result = preparedStatement.getGeneratedKeys();
					if(result.next()){
						int key = result.getInt(1);
						return key;
					}
				}
				return -1;
			} catch (SQLException e) {
				e.printStackTrace();
				throw new DataAccessException(e.getMessage(), e);
			}
			finally{
				ConnectionFactory.closeAll(connection, preparedStatement, result);
			}
		} else {
			throw new EmptyParamException("productType");
		}
	}
	
	public ProductType[] getAllProductTypes() throws DataAccessException {
		
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;
		List<ProductType> productTypes = new ArrayList<ProductType>(); 
		try {
			String insertQuery = "SELECT PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME, PRODUCT_TYPE_DESCRIPTION FROM "+TABLE_NAME_PRODUCT_TYPE;
			connection = ConnectionFactory.getConnection();
			statement = connection.createStatement();
			result = statement.executeQuery(insertQuery);
			while(result.next()){
				ProductType productType = new ProductType();
				productType.setProductTypeId(result.getInt("PRODUCT_TYPE_ID"));
				productType.setProductTypeName(result.getString("PRODUCT_TYPE_NAME"));
				productType.setProductTypeDescription(result.getString("PRODUCT_TYPE_DESCRIPTION"));
				productTypes.add(productType);
			}
			return (ProductType[]) productTypes.toArray(new ProductType[productTypes.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException(e.getMessage(), e);
		}
		finally{
			ConnectionFactory.closeAll(connection, statement, result);
		}
	}
	
	public ProductType getProductTypeById(int productTypeId) throws DataAccessException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet result = null;
		try {
			String insertQuery = "SELECT PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME, PRODUCT_TYPE_DESCRIPTION FROM "+TABLE_NAME_PRODUCT_TYPE+" WHERE PRODUCT_TYPE_ID=?";
			connection = ConnectionFactory.getConnection();
			preparedStatement = connection.prepareStatement(insertQuery);
			preparedStatement.setInt(1, productTypeId);
			result = preparedStatement.executeQuery();
			if(result.next()){
				ProductType productType = new ProductType();
				productType.setProductTypeId(result.getInt("PRODUCT_TYPE_ID"));
				productType.setProductTypeName(result.getString("PRODUCT_TYPE_NAME"));
				productType.setProductTypeDescription(result.getString("PRODUCT_TYPE_DESCRIPTION"));
				return productType;
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException(e.getMessage(), e);
		}
		finally{
			ConnectionFactory.closeAll(connection, preparedStatement, result);
		}
	}

	public void updateProductType(ProductType productType) throws DataAccessException {
		if(productType!=null && productType.getProductTypeId()>0 && !Validator.isNullOrEmpty(productType.getProductTypeName())) {
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet result = null;
			try {
				String insertQuery = "UPDATE "+TABLE_NAME_PRODUCT_TYPE+" SET PRODUCT_TYPE_NAME=?, PRODUCT_TYPE_DESCRIPTION=? " +
						"WHERE PRODUCT_TYPE_ID=?";
				connection = ConnectionFactory.getConnection();
				preparedStatement = connection.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
				preparedStatement.setString(1, productType.getProductTypeName());
				preparedStatement.setString(2, productType.getProductTypeDescription());
				preparedStatement.setInt(3, productType.getProductTypeId());
				int count = preparedStatement.executeUpdate();
				if(count<=0) {
					throw new DataAccessException("Update Failed");
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new DataAccessException(e.getMessage(), e);
			}
			finally{
				ConnectionFactory.closeAll(connection, preparedStatement, result);
			}
		} else {
			throw new EmptyParamException("productType");
		}
	}
	
}
